package com.apes.scm.invoice.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.invoice.model.InvoiceItem;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-01-23 16:07
 */
public interface InvoiceItemRepository extends CustomRepository<InvoiceItem, Long> {

    @Query(
            value = "select new map(t.id as invoiceItemId, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty as qty, t.freezeQty as freezeQty, t.qty-t.freezeQty-t.settledQty as pendingQty , " +
                    "k.id as uomId, k.name as uomName, j.id as taxId, j.name as taxName, t.originItemId as originItemId," +
                    "f.createDate as occurrenceDate,f.dept as dept," +
                    "(case when f.sourceBusinessType='Voucher' then (select v.carLicense from VoucherExtension v where v.id=t.sourceItemId) " +
                    " else null end) as carLicense,  " +
                    "f.origin as origin, f.originBusinessType as originBusinessType, t.discount as discount) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g left join t.tax j left join t.uom k " +
                    "where f.state = 0 and f.contract.id = ?1 and t.done = ?2",
            countQuery = "select count(*) from InvoiceItem t left join t.invoice f where f.contract.id = ?1 and t.done = ?2"
    )
    Page<Map<String, Object>> findInvoiceItemsByContractIdAndIsNotDone(String contractId, boolean done, Pageable pageable);

    @Query(
            value = "select new map(t.id as invoiceItemId,t.amtTax as amtTax, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty as qty, t.freezeQty as freezeQty, " +
                    "t.qty-t.freezeQty-t.settledQty as pendingQty , " +
                    "k.id as uomId, k.name as uomName, j.id as taxId, j.name as taxName, t.originItemId as originItemId," +
                    "f.createDate as occurrenceDate,f.dept as dept," +
                    "(case when f.sourceBusinessType='Voucher' then (select v.carLicense from VoucherExtension v where v.id=t.sourceItemId) else null end) as carLicense,  " +
                    "f.origin as origin, f.originBusinessType as originBusinessType, t.discount as discount,t.floorPrice as floorPrice) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g left join t.tax j left join t.uom k " +
                    "where f.state = 0 and f.company.id = :companyId and f.partner.id = :partnerId " +
                    "  and f.inventoryMode= :inventoryMode and t.done = :done and t.createDate >= :reconcileBeginDate and t.createDate <= :reconcileEndDate",
            countQuery = "select count(*) from InvoiceItem t left join t.invoice f " +
                    " where f.state = 0 and f.company.id = :companyId " +
                    "  and f.partner.id = :partnerId and f.inventoryMode= :inventoryMode and t.done = :done"
    )
    Page<Map<String, Object>> findInvoiceItemsByIsNotDone(@Param("companyId")String companyId, @Param("partnerId")String partnerId,
                                                          @Param("inventoryMode") String inventoryMode, @Param("done") boolean done,
                                                          @Param("reconcileBeginDate") Date reconcileBeginDate, @Param("reconcileEndDate") Date reconcileEndDate,
                                                          Pageable pageable);

    @Query(
            value = "select new map(t.id as invoiceItemId,t.amtTax as amtTax, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty as qty, t.freezeQty as freezeQty, " +
                    "t.qty-t.freezeQty-t.settledQty as pendingQty , " +
                    "k.id as uomId, k.name as uomName, j.id as taxId, j.name as taxName, t.originItemId as originItemId," +
                    "f.createDate as occurrenceDate,f.dept as dept," +
                    "(case when f.sourceBusinessType='Voucher' then (select v.carLicense from VoucherExtension v where v.id=t.sourceItemId) else null end) as carLicense,  " +
                    "f.origin as origin, f.originBusinessType as originBusinessType, t.discount as discount,t.floorPrice as floorPrice,t.remark as note) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g left join t.tax j left join t.uom k " +
                    "where f.state = 0 and  t.done = :done and f.company.id = :companyId and f.partner.id = :partnerId " +
                    "  and f.inventoryMode= :contractId and t.createDate >= :reconcileBeginDate and t.createDate <= :reconcileEndDate",
            countQuery = "select count(*) from InvoiceItem t left join t.invoice f " +
                    " where f.state = 0 and  t.done = :done and f.company.id = :companyId and f.partner.id = :partnerId and f.contract.id= :contractId")
    Page<Map<String, Object>> findAllByPartnerAndCompanyAndContract(@Param("done") boolean done,String partnerId, String companyId, String contractId, Date reconcileBeginDate, Date reconcileEndDate, Pageable pageable);


    @Query(

            value = "select new map(case when f.originBusinessType ='ServicePlan' then (select a.insuranceReportNumber from RetailOrder a,SaleOrder b " +
                    "where b.source=a.id and b.origin=f.origin and ROWNUM = 1) else r.insuranceReportNumber end as supplierRef," +
                    "t.id as invoiceItemId, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty as qty, t.freezeQty as freezeQty, t.qty-t.freezeQty-t.settledQty as pendingQty , " +
                    "k.id as uomId, k.name as uomName, j.id as taxId, j.name as taxName, t.originItemId as originItemId,t.invoice.dept.id as deptId ,t.invoice.dept.name as deptName ,l.carLicense as carLicense," +
                    "f.origin as origin, f.originBusinessType as originBusinessType, f.source as source, f.sourceBusinessType as sourceBusinessType," +
                    "f.createDate as occurrenceDate,  t.discount as discount,t.amtTax as amtTax) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g left join t.tax j left join t.uom k " +
                    "left join RetailOrder r on r.id = f.origin left join FnCustomerExpand l on l.id = t.invoice.customerExpand " +
                    "where f.state = 0 and f.partner.id = ?1 and f.company.id = ?2  and f.inventoryMode = ?3 " +
                    "  and f.accountingMethod = ?4 and t.done = ?5 and  t.createDate >= ?6 and t.createDate<=?7 order by t.createDate",
            countQuery = "select count(*) from InvoiceItem t left join t.invoice f where f.state = 0 and f.partner.id = ?1 and f.company.id = ?2  and f.inventoryMode = ?3 and f.accountingMethod = ?4 and t.done = ?5 and  t.createDate >= ?6 and t.createDate<=?7  order by t.createDate"
    )
    Page<Map<String, Object>> findInvoiceItemsByLedgerUnique(String partnerId, String companyId, String inventoryMode,
                                                             String accountingMethod, boolean done, Date reconcileBeginDate,
                                                             Date reconcileEndDate, Pageable pageable);

    /**
     * fetch data by partner Id and dept Id and start date and end Date
     *
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end Date
     * @param partnerId          partner id
     * @param done               done
     * @return Page<Map < String, Object>>
     */
    @Query(
            value = "select new map(t.id as invoiceItemId,f.id as invoiceId, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty-t.freezeQty-t.settledQty as qty, t.freezeQty as freezeQty, " +
                    "t.qty-t.freezeQty-t.settledQty as pendingQty , k.id as uomId, k.name as uomName, t.originItemId as originItemId, f.company.id as companyId, " +
                    "f.company.party.name as companyName, f.origin as origin, f.originBusinessType as originBusinessType, t.discount as discount,to_char(t.createDate,'yyyy-mm-dd') as occurrenceDate," +
                    "x.id as taxId,x.name as taxName,t.floorPrice as floorPrice,t.discount as discountAmount ) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g  left join t.uom k left JOIN t.tax x " +
                    "where f.state = 0 and f.company.id in (?1) and f.partner.id = ?2 and t.done = ?3 and  f.thirdTime >= ?4 and f.thirdTime<=?5 and  t.invoice.inventoryMode = ?6"
    )
    List<Map<String, Object>> findInvoiceItemsByPartnerIdAndPlat(List companyId,String partnerId, boolean done, Date reconcileBeginDate, Date reconcileEndDate,String cooperation);

    @Query(
            value = "select new map(t.id as invoiceItemId,f.id as invoiceId, g.id as productId, g.name as productName, t.priceUnit as priceUnit, t.qty as qty, t.freezeQty as freezeQty, t.qty-t.freezeQty-t.settledQty as pendingQty , " +
                    "k.id as uomId, k.name as uomName, t.originItemId as originItemId, f.company.id as companyId, f.company.party.name as companyName," +
                    " (case  when f.originBusinessType in ('ServicePlan','WorkOrder') then " +
                    " (case when ( select o.id from RetailOrder o where o.source=f.origin and o.orderType='server' ) =null then f.origin else ( select o.id from RetailOrder o where o.source=f.origin and o.orderType='server' ) end ) " +
                    "  else  f.origin end) as origin," +
                    " (case  when f.originBusinessType in ('ServicePlan','WorkOrder') then " +
                    " (case when ( select o.id from RetailOrder o where o.source=f.origin and o.orderType='server' ) =null then (select name from BusinessType where id= f.originBusinessType)  else '零售单' end ) " +
                    " else  (select name from BusinessType where id= f.originBusinessType)  end) as originBusinessType," +
                    "t.discount as discount,to_char(t.createDate,'yyyy-mm-dd') as occurrenceDate," +
                    "x.id as taxId,x.name as taxName,t.floorPrice  as floorPrice,t.discount as discountAmount ) " +
                    "from InvoiceItem t left join t.invoice f left join t.product g  left join t.uom k left JOIN t.tax x " +
                    "where f.thirdTime is not null and  f.state = 0 and f.partner.id = ?1" +
                    "  and t.done = ?2 and  t.createDate >= ?3 and t.createDate<=?4 and f.company.id <>'1001' and t.invoice.inventoryMode = ?5 "
    )
    List<Map<String, Object>> findInvoiceItemsByPartnerIdAndPlatThirdTime(String partnerId, boolean done, Date reconcileBeginDate, Date reconcileEndDate,String cooperation);

    /**
     * fetch data by partner Id and contract type Id and start date and end Date
     *
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end Date
     * @param partnerId          partner id
     * @param done               done
     * @return Page<Map < String, Object>>
     */
    @Query(
            value = "select t " +
                    "from InvoiceItem t left join t.invoice f left join t.product g  left join t.uom k left JOIN t.tax x  " +
                    "where f.state = 0 and f.partner.id = ?1 and f.company.id=?2 and f.contract.contractType.id=?3 " +
                    " and t.done = ?4 and  t.createDate >= ?5 and t.createDate<=?6 "
    )
    List<InvoiceItem> findInvoiceItemsByPartnerId(String partnerId,String companyId, String contractType ,boolean done, Date reconcileBeginDate, Date reconcileEndDate);

    @Query(value = "SELECT B.* FROM ACC_INVOICE A,ACC_INVOICE_ITEM B WHERE A.ID = B.INVOICE_ID AND A.COMPANY_ID = :partnerId AND A.PARTNER_ID = :supplierId " +
            "AND A.INVENTORY_MODE = :inventoryMode AND A.CREATE_DATE >= to_date(:startDate,'yyyy-MM-dd') AND A.CREATE_DATE < to_date(:endDate,'yyyy-MM-dd') + 1",nativeQuery = true)
    List<InvoiceItem> findByRebatePolicy(@Param("startDate") String startDate, @Param("endDate") String endDate, @Param("supplierId") String supplierId,
                                         @Param("partnerId") String partnerId, @Param("inventoryMode") String inventoryMode);
}
